#!/usr/bin/env python
"""
用户脚本迁移脚本
将用户脚本从数据库迁移到文件系统
"""
import sys
import os
import sqlite3
import json
from pathlib import Path

# 添加项目根目录到路径
sys.path.insert(0, os.path.dirname(os.path.dirname(__file__)))

from services.shared.file_utils import save_user_script
from services.shared.models import db
from services.shared.models.user import User
from services.shared.models.script import UserScript, ScriptFolder


def get_db_path():
    """获取数据库路径"""
    return os.path.join(os.path.dirname(os.path.dirname(__file__)), 'data', 'sqlite', 'xbyjob.db')


def migrate_scripts_to_filesystem():
    """将数据库中的脚本迁移到文件系统"""
    db_path = get_db_path()
    
    if not os.path.exists(db_path):
        print(f"数据库文件不存在: {db_path}")
        return False
    
    try:
        # 连接数据库
        conn = sqlite3.connect(db_path)
        cursor = conn.cursor()
        
        # 获取所有用户脚本
        cursor.execute("SELECT id, name, content, user_id FROM user_scripts")
        scripts = cursor.fetchall()
        
        # 获取用户信息（用户名）
        cursor.execute("SELECT id, username FROM users")
        users = {row[0]: row[1] for row in cursor.fetchall()}
        
        print(f"找到 {len(scripts)} 个用户脚本需要迁移")
        
        # 迁移每个脚本
        migrated_count = 0
        for script_id, script_name, script_content, user_id in scripts:
            username = users.get(user_id, f"user_{user_id}")
            
            try:
                # 保存到文件系统
                script_path = save_user_script(username, script_name, script_content)
                print(f"已迁移脚本: {username}/{script_name}.py")
                migrated_count += 1
            except Exception as e:
                print(f"迁移脚本失败 {username}/{script_name}: {str(e)}")
        
        print(f"成功迁移 {migrated_count}/{len(scripts)} 个脚本")
        
        # 关闭数据库连接
        conn.close()
        
        return True
        
    except Exception as e:
        print(f"迁移过程中发生错误: {str(e)}")
        return False


def remove_content_column():
    """移除user_scripts表中的content字段"""
    db_path = get_db_path()
    
    try:
        # 连接数据库
        conn = sqlite3.connect(db_path)
        cursor = conn.cursor()
        
        # 检查content字段是否存在
        cursor.execute("PRAGMA table_info(user_scripts)")
        columns = [column[1] for column in cursor.fetchall()]
        
        if 'content' not in columns:
            print("content字段已不存在")
            conn.close()
            return True
        
        # SQLite不支持直接删除列，需要重建表
        print("正在重建user_scripts表...")
        
        # 1. 创建新表（不包含content字段）
        cursor.execute("""
            CREATE TABLE user_scripts_new (
                id INTEGER PRIMARY KEY,
                name VARCHAR(200) NOT NULL,
                description TEXT,
                folder_id INTEGER,
                user_id INTEGER NOT NULL,
                language VARCHAR(50) NOT NULL DEFAULT 'python',
                is_active BOOLEAN NOT NULL DEFAULT 1,
                tags TEXT,
                created_at DATETIME NOT NULL,
                updated_at DATETIME NOT NULL,
                FOREIGN KEY (folder_id) REFERENCES script_folders (id),
                FOREIGN KEY (user_id) REFERENCES users (id)
            )
        """)
        
        # 2. 复制数据（不包含content字段）
        cursor.execute("""
            INSERT INTO user_scripts_new 
            SELECT id, name, description, folder_id, user_id, language, is_active, tags, created_at, updated_at
            FROM user_scripts
        """)
        
        # 3. 删除旧表
        cursor.execute("DROP TABLE user_scripts")
        
        # 4. 重命名新表
        cursor.execute("ALTER TABLE user_scripts_new RENAME TO user_scripts")
        
        # 5. 重新创建索引
        cursor.execute("CREATE INDEX ix_user_scripts_folder_id ON user_scripts (folder_id)")
        cursor.execute("CREATE INDEX ix_user_scripts_user_id ON user_scripts (user_id)")
        
        # 提交更改
        conn.commit()
        conn.close()
        
        print("成功移除content字段")
        return True
        
    except Exception as e:
        print(f"移除content字段时发生错误: {str(e)}")
        return False


def main():
    """主函数"""
    print("开始迁移用户脚本...")
    
    # 1. 迁移脚本到文件系统
    if not migrate_scripts_to_filesystem():
        print("脚本迁移失败")
        return False
    
    # 2. 移除数据库中的content字段
    if not remove_content_column():
        print("移除content字段失败")
        return False
    
    print("用户脚本迁移完成!")
    return True


if __name__ == '__main__':
    success = main()
    sys.exit(0 if success else 1)